﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessXiYiDaoRu
// 文件功能描述：洗衣券导入数据访问层
//
// 
// 创建标识：2012-07-17 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DbAdapter.sqldbAdapter;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace DAO.WaiGaoQiao
{
    public class DataAccessXiYiDaoRu
    {
        public string ExcelDataSource(string filepath, string sheetname, string Operator)
        {
            /*
             *将excel中的数据导入到DataSet中 
             */
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn1 = new OleDbConnection(strConn);
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
            DataSet ds = new DataSet();
            oada.Fill(ds);

            SqlConnection conn = new SqlConnection(GetConntionString.conntionstring());
            SqlConnection myConn = conn;
            myConn.Open();
            SqlCommand myComm = new SqlCommand();
            SqlTransaction myTran;
            //创建一个事务
            myTran = myConn.BeginTransaction();
            string query = "";

            sqldb db = new sqldb();
            SqlConnection conns = new SqlConnection(GetConntionString.conntionstring());
            object obj;
            object objBalance;
            try
            {
                //从此开始，基于该连接的数据操作都被认为是事务的一部分
                //下面绑定连接和事务对象
                myComm.Connection = myConn;
                myComm.Transaction = myTran; //定位到pubs数据库
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (ds.Tables[0].Rows[i + 2][1].ToString() == "" || ds.Tables[0].Rows[i + 2][0].ToString() == "合计")
                    {
                        break;
                    }
                    query = "select COUNT(*) from Card_Card where Status=0 and PID=" + ds.Tables[0].Rows[i + 2][2].ToString().Trim();
                    myComm.CommandText = query;
                    obj = myComm.ExecuteScalar();
                    if (Convert.ToInt32(obj) == 0)
                    {
                        return ds.Tables[0].Rows[i + 2][2].ToString().Trim();
                    }
                    if (ds.Tables[0].Rows[i + 2][9].ToString() == "")
                    {
                        continue;
                    }
                    query = "select BalanceT from Card_Card where Status=0 and PID=" + ds.Tables[0].Rows[i + 2][2].ToString().Trim();
                    myComm.CommandText = query;
                    objBalance = myComm.ExecuteScalar();
                    query = "update Card_Card set BalanceT=BalanceT+" + Convert.ToInt32(Convert.ToDecimal(ds.Tables[0].Rows[i + 2][9]) * 100.00M) + " where Status=0 and PID=" + ds.Tables[0].Rows[i + 2][2].ToString().Trim();
                    myComm.CommandText = query;
                    myComm.ExecuteNonQuery();
                    query = "insert into Con_xiyijilu(PID,Count_xiyi,Balace_tq,Balace_th,Jilu_ID) values(" + ds.Tables[0].Rows[i + 2][2].ToString().Trim() +
                        ",'" + Convert.ToInt32(Convert.ToDecimal(ds.Tables[0].Rows[i + 2][9]) * 100.00M) + "','" + objBalance + "','" +
                        Convert.ToInt32(objBalance) + Convert.ToInt32(Convert.ToDecimal(ds.Tables[0].Rows[i + 2][9]) * 100.00M) + "',1)";
                    myComm.CommandText = query;
                    myComm.ExecuteNonQuery();
                }
                myTran.Commit();
            }
            catch (Exception err)
            {
                throw new ApplicationException("事务操作出错，系统信息：" + err.Message);
            }
            finally
            {
                myConn.Close();
            }
            return "1";
        }
    }
}
